﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace CommonTools
{
    public partial class ExcelImportForm : Form
    {
        public ExcelImportForm()
        {
            InitializeComponent();
        }

        private void btnSelectFile_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();
        }


        private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            this.txtFile.Text = openFileDialog1.FileName;
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + this.txtFile.Text + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=0'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            DataTable excelDT = null;
            excelDT = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            List<string> excelSheetsName = new List<string>();
            // Add the sheet name to the string array.

            OleDbConnection conn1 = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\Projects\Projects\DB\FamilyDB.mdb'");
            OleDbCommand cmd1 = conn1.CreateCommand();
            conn1.Open();
            foreach (DataRow row in excelDT.Rows)
            {

                if (row["TABLE_NAME"].ToString().Contains("日常支出"))
                {
                    //_data = FetchSheetData(conn, "在册员工花名册", "");
                    string sql = "select * from [日常支出$]";
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    ad.Fill(dt);
                    this.dataGridView1.DataSource = dt;

                    foreach (DataRow item in dt.Rows)
                    {
                        sql = "insert into T_PayRecords(PayDate,PayItem," +
                            " PayObject,PayCategoryOID,PayNecessity,"+
                            " PaySum,PayMemo) values('"+DateTime.Parse( item[1].ToString())+"',"+
                        " '"+ item[2] +"',3,24,1,"+(double)item[3]+",'"+item[5].ToString()+"')";

                        cmd1.CommandText = sql;
                        cmd1.ExecuteNonQuery();
                    }


                    break;
                }
            }
        }
    }
}
